package com.sqlcheck.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sqlcheck.entity.SqlStatement;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

@Mapper
public interface SqlStatementRepository extends BaseMapper<SqlStatement> {

        /**
         * 批量插入SQL语句
         * 注意：此方法在失败时直接抛出异常，不提供降级为单条插入的机制
         * 
         * @param statements SQL语句列表
         * @return 实际插入的记录数
         * @throws BatchOperationFailedException 当批量插入失败时
         */
        int batchInsert(@Param("statements") List<SqlStatement> statements);

        /**
         * 根据任务ID和语句序号查询
         */
        @Select("SELECT * FROM sql_statements WHERE task_id = #{taskId} AND statement_number = #{statementNumber} AND deleted = 0")
        SqlStatement selectByTaskAndNumber(@Param("taskId") Long taskId,
                        @Param("statementNumber") Integer statementNumber);

        /**
         * 根据任务ID查询所有SQL语句
         * 使用明确的字段映射避免枚举转换错误
         */
        @Select("SELECT id, task_id, statement_number, sql_content as content, sql_content_hash as contentHash, " +
                        "sql_type, source_type, ddl_type, source_file, line_number, column_number, " +
                        "class_name, method_name, java_annotation_type, mybatis_xml_element, " +
                        "primary_table, affected_tables as affectedTablesText, " +
                        "database_objects as databaseObjectsJson, extraction_metadata as extractionMetadataJson, " +
                        "is_native_query, is_derived_query, created_at, updated_at, deleted " +
                        "FROM sql_statements WHERE task_id = #{taskId} AND deleted = 0 ORDER BY statement_number")
        List<SqlStatement> selectByTaskId(@Param("taskId") Long taskId);

        /**
         * 根据表名查询相关SQL语句
         * 使用LIKE查询affected_tables字段（逗号分隔的字符串）
         */
        @Select("SELECT * FROM sql_statements WHERE (primary_table = #{tableName} OR affected_tables LIKE CONCAT('%', #{tableName}, '%')) AND deleted = 0")
        List<SqlStatement> selectByTableName(@Param("tableName") String tableName);

        /**
         * 根据任务ID统计不同类型的SQL数量
         */
        @Select("SELECT sql_type, COUNT(*) as count FROM sql_statements WHERE task_id = #{taskId} AND deleted = 0 GROUP BY sql_type")
        List<Map<String, Object>> countByTaskIdAndSqlType(@Param("taskId") Long taskId);

        /**
         * 根据任务ID统计不同来源类型的SQL数量
         */
        @Select("SELECT source_type, COUNT(*) as count FROM sql_statements WHERE task_id = #{taskId} AND deleted = 0 GROUP BY source_type")
        List<Map<String, Object>> countByTaskIdAndSourceType(@Param("taskId") Long taskId);

        /**
         * 根据任务ID统计DDL类型的SQL数量（仅限DDL类型）
         */
        @Select("SELECT ddl_type, COUNT(*) as count FROM sql_statements WHERE task_id = #{taskId} AND sql_type = 'DDL' AND ddl_type IS NOT NULL AND deleted = 0 GROUP BY ddl_type")
        List<Map<String, Object>> countByTaskIdAndDdlType(@Param("taskId") Long taskId);

        /**
         * 获取任务涉及的所有表名
         */
        @Select("SELECT DISTINCT primary_table FROM sql_statements WHERE task_id = #{taskId} AND primary_table IS NOT NULL AND deleted = 0")
        List<String> selectAffectedTablesByTaskId(@Param("taskId") Long taskId);

        /**
         * 根据内容哈希查找重复SQL
         */
        @Select("SELECT * FROM sql_statements WHERE task_id = #{taskId} AND sql_content_hash = #{contentHash} AND deleted = 0")
        List<SqlStatement> selectByTaskIdAndContentHash(@Param("taskId") Long taskId,
                        @Param("contentHash") String contentHash);

        /**
         * 获取任务中SQL语句总数
         */
        @Select("SELECT COUNT(*) FROM sql_statements WHERE task_id = #{taskId} AND deleted = 0")
        int countByTaskId(@Param("taskId") Long taskId);

        /**
         * 批量更新SQL语句的分析结果关联
         */
        @Update("UPDATE sql_statements SET extraction_metadata = #{metadataJson} WHERE id = #{id}")
        int updateExtractionMetadata(@Param("id") Long id, @Param("metadataJson") String metadataJson);

        /**
         * 根据SQL类型和来源类型查询
         */
        @Select("SELECT * FROM sql_statements WHERE task_id = #{taskId} AND sql_type = #{sqlType} AND source_type = #{sourceType} AND deleted = 0 ORDER BY statement_number")
        List<SqlStatement> selectByTaskIdAndTypes(@Param("taskId") Long taskId,
                        @Param("sqlType") SqlStatement.SqlType sqlType,
                        @Param("sourceType") SqlStatement.SourceType sourceType);

        /**
         * 根据Java注解类型查询
         */
        @Select("SELECT * FROM sql_statements WHERE task_id = #{taskId} AND java_annotation_type = #{annotationType} AND deleted = 0 ORDER BY statement_number")
        List<SqlStatement> selectByTaskIdAndJavaAnnotationType(@Param("taskId") Long taskId,
                        @Param("annotationType") SqlStatement.JavaAnnotationType annotationType);

        /**
         * 根据MyBatis XML元素类型查询
         */
        @Select("SELECT * FROM sql_statements WHERE task_id = #{taskId} AND mybatis_xml_element = #{xmlElement} AND deleted = 0 ORDER BY statement_number")
        List<SqlStatement> selectByTaskIdAndMybatisXmlElement(@Param("taskId") Long taskId,
                        @Param("xmlElement") SqlStatement.MybatisXmlElement xmlElement);

        /**
         * 关键词搜索SQL内容
         */
        @Select("SELECT * FROM sql_statements WHERE task_id = #{taskId} AND " +
                        "(sql_content LIKE CONCAT('%', #{keyword}, '%') OR " +
                        "class_name LIKE CONCAT('%', #{keyword}, '%') OR " +
                        "method_name LIKE CONCAT('%', #{keyword}, '%')) " +
                        "AND deleted = 0 ORDER BY statement_number")
        List<SqlStatement> searchByTaskIdAndKeyword(@Param("taskId") Long taskId, @Param("keyword") String keyword);

        /**
         * 软删除指定任务的所有SQL语句记录
         * 用于重试任务时清理历史数据，避免唯一约束冲突
         */
        @Update("UPDATE sql_statements SET deleted = 1, updated_at = NOW() WHERE task_id = #{taskId} AND deleted = 0")
        int softDeleteByTaskId(@Param("taskId") Long taskId);
}